import pymysql


class DBTools(object):
    @staticmethod
    def __create_connet():
        conn = pymysql.connect(host="211.103.136.244", port=7061,
                               user="student", password="iHRM_student_2022",
                               database="ihrm", charset="utf8")
        return conn

    # 查询一行方法
    @staticmethod
    def query_one(sql):
        conn = None
        cursor = None
        ret = None
        try:
            conn = DBTools.__create_connet()
            cursor = conn.cursor()
            cursor.execute(sql)
            ret = cursor.fetchone()
        except Exception as e:
            print("出现错误！")
            raise
        finally:
            cursor.close()
            conn.close()
            return ret

    # 查询所有方法
    @staticmethod
    def query_all(sql):
        conn = None
        cursor = None
        ret = None
        try:
            conn = DBTools.__create_connet()
            cursor = conn.cursor()
            cursor.execute(sql)
            ret = cursor.fetchall()
        except Exception as e:
            print("出现错误！")
            raise
        finally:
            cursor.close()
            conn.close()
            return ret

    # 查询指定行数方法
    @staticmethod
    def query_many(sql, idx, n):
        conn = None
        cursor = None
        ret = None
        try:
            conn = DBTools.__create_connet()
            cursor = conn.cursor()
            cursor.execute(sql)
            cursor.rownumber = idx - 1
            ret = cursor.fetchmany(n)
        except Exception as e:
            print("出现错误！", e)
            raise
        finally:
            cursor.close()
            conn.close()
            return ret

    # 增删改查方法。
    @staticmethod
    def iud(sql):
        conn = None
        cursor = None
        ret = None
        try:
            conn = DBTools.__create_connet()
            cursor = conn.cursor()
            ret = cursor.execute(sql)
            conn.commit()
        except Exception as e:
            conn.rollback()
            print("出现错误！", e)
            raise
        finally:
            cursor.close()
            conn.close()
            return f"执行成功影响{ret}行:"


if __name__ == '__main__':
    ret = DBTools.iud("insert into t_book(id,title,pub_date)values(101,'穿越到异世界','2022-12-12')")
    print(ret)
